package com.abc.recycling.mapper;

import com.abc.recycling.model.User;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import java.util.List;
import java.time.LocalDateTime;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface UserMapper {
    
    Page<User> findByRoleAndKeyword(@Param("role") String role, @Param("keyword") String keyword, @Param("pageable") Pageable pageable);
    
    User findById(@Param("id") Long id);
    
    User findByUsername(@Param("username") String username);
    
    @Select("SELECT * FROM user WHERE role = #{role} AND status = 1")
    List<User> findByRole(String role);
    
    @Insert("INSERT INTO user (username, password, real_name, phone, email, address, role, status, create_time, update_time) " +
            "VALUES (#{username}, #{password}, #{realName}, #{phone}, #{email}, #{address}, #{role}, #{status}, #{createTime}, #{updateTime})")
    void insert(User user);
    
    @Update("UPDATE user SET username = #{username}, " +
            "password = #{password}, " +
            "real_name = #{realName}, " +
            "phone = #{phone}, " +
            "email = #{email}, " +
            "address = #{address}, " +
            "service_area = #{serviceArea}, " +
            "update_time = #{updateTime} " +
            "WHERE id = #{id}")
    void update(User user);
    
    @Update("UPDATE user SET status = #{status}, update_time = #{updateTime} WHERE id = #{id}")
    void updateStatus(User user);
    
    int deleteById(Long id);
    
    Page<User> search(@Param("role") String role, @Param("keyword") String keyword, @Param("pageable") Pageable pageable);
    
    @Select("SELECT COUNT(DISTINCT u.id) FROM user u " +
            "JOIN recycling_order o ON u.id = o.user_id " +
            "WHERE o.create_time >= #{start} AND o.create_time < #{end}")
    Integer countActiveUsersByDateRange(LocalDateTime start, LocalDateTime end);
    
    @Update("UPDATE user SET avatar = #{avatar} WHERE id = #{id}")
    void updateAvatar(User user);

    @Update("UPDATE user SET last_active_time = #{lastActiveTime} WHERE id = #{id}")
    void updateLastActiveTime(@Param("id") Long id, @Param("lastActiveTime") LocalDateTime lastActiveTime);

    @Update("UPDATE user SET average_rating = #{rating} WHERE id = #{id}")
    void updateAverageRating(@Param("id") Long id, @Param("rating") Double rating);

    @Update("UPDATE user SET total_orders = #{totalOrders}, monthly_orders = #{monthlyOrders}, " +
            "monthly_income = #{monthlyIncome} WHERE id = #{id}")
    void updateOrderStats(@Param("id") Long id, @Param("totalOrders") Long totalOrders, 
            @Param("monthlyOrders") Long monthlyOrders, @Param("monthlyIncome") Double monthlyIncome);

    @Update("UPDATE user SET password = #{password}, update_time = NOW() WHERE id = #{id}")
    void updatePassword(User user);

    @Select("SELECT COUNT(*) FROM user WHERE username = #{username}")
    int countByUsername(@Param("username") String username);

    @Select("SELECT COUNT(*) FROM user WHERE phone = #{phone}")
    int countByPhone(@Param("phone") String phone);

    @Select("SELECT COUNT(*) FROM user WHERE email = #{email}")
    int countByEmail(@Param("email") String email);

    List<User> findByRoleAndKeywordWithoutPaging(@Param("role") String role, @Param("keyword") String keyword);

    @Select("SELECT * FROM user WHERE phone = #{phone}")
    User findByPhone(@Param("phone") String phone);

    @Select("SELECT * FROM user WHERE email = #{email}")
    User findByEmail(@Param("email") String email);

    @Select("SELECT COUNT(*) FROM user WHERE role = #{role}")
    Long countByRole(@Param("role") String role);

    @Select("SELECT COUNT(DISTINCT u.id) FROM user u " +
            "JOIN recycling_order o ON u.id = o.collector_id " +
            "WHERE u.role = 'COLLECTOR' " +
            "AND o.create_time BETWEEN #{startTime} AND #{endTime}")
    Long countActiveCollectorsByDateRange(@Param("startTime") LocalDateTime startTime,
                                        @Param("endTime") LocalDateTime endTime);

    @Select("SELECT AVG(average_rating) FROM user WHERE role = 'COLLECTOR' AND average_rating IS NOT NULL")
    Double getAverageCollectorRating();

    @Select("SELECT COUNT(*) FROM user WHERE role = #{role} AND status = 1")
    Long countActiveUsersByRole(@Param("role") String role);

    @Select("SELECT AVG(average_rating) FROM user WHERE role = 'USER' AND average_rating IS NOT NULL")
    Double getAverageUserRating();

    Long countCollectors();
    Long countByRegion(@Param("region") String region);

    User findCollectorWithStats(@Param("id") Long id);
} 